package ua.maxicash.dao;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import ua.maxicash.vo.Work;
import ua.maxicash.vo.Worker;
import ua.maxicash.vo.WorkingDay;

import java.util.*;

@Repository("calendarDao")
public class CalendarDaoImpl implements CalendarDao {

    @Autowired
    HibernateTemplate hibernateTemplate;

    @Override
    @Transactional
    public List<java.util.Date> getWorkersCalendarForMonth(int month, int year, Long idUser) {

        java.util.Date dateStart = null;
        java.util.Date dateFinish = null;

        Calendar newCal = new GregorianCalendar();

        newCal.set(year, month, 1);

        dateStart = newCal.getTime();

        int days = newCal.getActualMaximum(Calendar.DAY_OF_MONTH);
        newCal.set(year, month, days);

        dateFinish = newCal.getTime();

        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        SQLQuery query = session
                .createSQLQuery("SELECT WORKINGDAY.WORKDATE FROM WORKINGDAY"
                        + " inner Join Work on WORKINGDAY.ID_WORK = Work.ID_WORK"
                        + " inner Join Work_Worker on Work.ID_WORK = Work_Worker.ID_WORK"
                        + " inner join worker on Work_Worker.ID_USER = :idUser"
                        + " where WORKDATE between :date1 AND :date2  "
                        + " group by WORKDATE");
        //query.addEntity(WorkingDay.class);

        query.setLong("idUser", idUser);
        query.setDate("date1", dateStart);
        query.setDate("date2", dateFinish);

        return query.list();
    }

    @Override
    @Transactional
    public List<java.util.Date> getEmployersCalendarForMonth(int month, int year, Long idUser) {

        java.util.Date dateStart = null;
        java.util.Date dateFinish = null;

        Calendar newCal = new GregorianCalendar();

        newCal.set(year, month, 1);

        dateStart = newCal.getTime();

        int days = newCal.getActualMaximum(Calendar.DAY_OF_MONTH);
        newCal.set(year, month, days);

        dateFinish = newCal.getTime();

        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        SQLQuery query = session
                .createSQLQuery("SELECT WORKINGDAY.WORKDATE FROM WORKINGDAY"
                        + " inner Join Work on WORKINGDAY.ID_WORK = Work.ID_WORK"
                        + " where Work.ID_USER = :idUser"
                        + " AND (WORKDATE between :date1 AND :date2 ) "
                        + " group by WORKDATE");
        //query.addEntity(WorkingDay.class);

        query.setLong("idUser", idUser);
        query.setDate("date1", dateStart);
        query.setDate("date2", dateFinish);

        return query.list();
    }

    @Override
    @Transactional
    public Set<WorkingDay> getWorkersCalendarForDay(int day, int month, int year, Long idUser) {

        java.util.Date date = null;

        Calendar newCal = new GregorianCalendar();
        //newCal.setTimeInMillis(System.currentTimeMillis());
        newCal.set(year, month, day);

        date = newCal.getTime();


        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        SQLQuery query = session
                .createSQLQuery("SELECT WORKINGDAY.* FROM WORKINGDAY"
                        + " inner Join Work on WORKINGDAY.ID_WORK = Work.ID_WORK"
                        + " inner Join Work_Worker on Work.ID_WORK = Work_Worker.ID_WORK"
                        + " inner join worker on Work_Worker.ID_USER = :idUser"
                        + " where WORKDATE = :date1 ");
        query.addEntity(WorkingDay.class);
        query.setLong("idUser", idUser);
        query.setDate("date1", date);
        query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        //todo FIX

        Set<WorkingDay> wds = new HashSet<WorkingDay>(query.list());

        for (WorkingDay wd : wds) {
            wd.getWork();
            Hibernate.initialize(wd.getWork());
        }
        return wds;


    }

    @Override
    @Transactional
    public Set<WorkingDay> getEmployersCalendarForDay(int day, int month, int year, Long idUser) {

        java.util.Date date = null;

        Calendar newCal = new GregorianCalendar();
        //newCal.setTimeInMillis(System.currentTimeMillis());
        newCal.set(year, month, day);

        date = newCal.getTime();


        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        SQLQuery query = session
                .createSQLQuery("SELECT WORKINGDAY.* FROM WORKINGDAY"
                        + " inner Join Work on WORKINGDAY.ID_WORK = Work.ID_WORK"
                        + " where Work.ID_USER = :idUser"
                        + " and WORKDATE = :date1 ");
        query.addEntity(WorkingDay.class);
        query.setLong("idUser", idUser);
        query.setDate("date1", date);
        query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

        //todo FIX
        Set<WorkingDay> wds = new HashSet<WorkingDay>(query.list());

        for (WorkingDay wd : wds) {
            wd.getWork();
            Hibernate.initialize(wd.getWork());
        }
        return wds;


    }

    @Override
    @Transactional
    public Set<Work> getNearestWorkerWorksForData(java.util.Date date, int amount, Long idWorker) {

        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        Criteria criteria = session.createCriteria(Work.class);


        criteria.setMaxResults(amount);

        DetachedCriteria dc = DetachedCriteria.forClass(Work.class);
        dc.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        dc.createAlias("applicants", "applAlias");
        dc.createAlias("schedule", "scheduleAlias");
        dc.add(Restrictions.eq("applAlias.idUser", idWorker));
        dc.add(Restrictions.ge("scheduleAlias.workDate", date));
        dc.setProjection(Projections.id());
        //criteria.createAlias("applicants","applAlias");
        //Criteria crit = criteria.createCriteria("schedule");
        //criteria.add(Restrictions.eq("applAlias.idUser", idWorker));
        //crit.add(Restrictions.ge("workDate", date));

        criteria.add(Subqueries.propertyIn("idWork", dc));
        List<Work> works = criteria.list();
        return new HashSet<Work>(works);

    }

    @Override
    public Set<Work> getNearestEmployerWorksForData(java.util.Date date,
                                                    int amount, Long idEmployer) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    @Transactional
    public Set<Work> getNearestRandomWorksForWorker(Date date, int amount, Long idWorker) {


        Session session = hibernateTemplate.getSessionFactory()
                .getCurrentSession();
        Criteria criteria = session.createCriteria(Work.class);


        if (amount > 0) {
            criteria.setMaxResults(amount);
        }

        DetachedCriteria dc = DetachedCriteria.forClass(Work.class);
        dc.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        dc.createAlias("applicants", "applAlias");
        dc.createAlias("schedule", "scheduleAlias");
        dc.add(Restrictions.ne("applAlias.idUser", idWorker));
        dc.add(Restrictions.ge("scheduleAlias.workDate", date));

        //Retrieving city of worker
        DetachedCriteria cityCrit = DetachedCriteria.forClass(Worker.class);
        //cityCrit.setProjection(Projections.projectionList()
        //.add(Projections.property("city"))
        //        .add(Projections.property("jobTypes"),"jobTypes")
        //);
        cityCrit.add(Restrictions.eq("idUser", idWorker));
        //dc.add(Subqueries.propertyIn("jobType",cityCrit));
        Worker worker = (Worker) hibernateTemplate.findByCriteria(cityCrit).get(0);
        dc.add(Restrictions.eq("city", worker.getCity()));
        dc.add(Restrictions.in("jobType", worker.getJobTypes()));

        dc.setProjection(Projections.id());
        //criteria.createAlias("applicants","applAlias");
        //Criteria crit = criteria.createCriteria("schedule");
        //criteria.add(Restrictions.eq("applAlias.idUser", idWorker));
        //crit.add(Restrictions.ge("workDate", date));

        criteria.add(Subqueries.propertyIn("idWork", dc));
        List<Work> works = criteria.list();
        return new HashSet<Work>(works);
    }

    @Override
    //TODO
    public Set<Work> getNearestRandomWorksForEmployer(Date date, int amount, Long idWorker) {
        return null;  //To change body of implemented methods use File | Settings | File Templates.
    }
}
